package com.globalresources.dao.hibernate;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowCallbackHandler;

import com.globalresources.commons.StringHelper;
import com.globalresources.commons.vo.UserAndDocumentWrapper;
import com.globalresources.dao.inface.IDocumentDao;
import com.globalresources.po.Document;
import com.globalresources.ui.struts.action.document.vo.PatientDocument;
import com.trg.dao.dao.standard.GenericDAOImpl;
import com.trg.search.Search;

/**
 * @author aaron.lu(luzhiyuan) E-mail:atyuan.lu@gmail.com
 * @since 创建时间：Jul 19, 2009 10:19:33 AM
 */
public class DocumentDaoHibernate extends GenericDAOImpl<Document, Integer> implements IDocumentDao {
	Logger logger = Logger.getLogger(this.getClass());
	private JdbcTemplate jdbcTemplate;
	
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	/**
	 * load documents 
	*@author aaron.lu(luzhiyuan)
	*@param userid
	*@param isSign
	*@param isView
	*@return
	 */
	public List<Document> getDocuments(Integer userid, Byte isSign, Byte isView){
		Search search = new Search();
		search.addFilterEqual("detailUserByUserId.id", userid);
		search.addFilterEqual("isSign", isSign);
		search.addFilterEqual("isView", isView);
		search.addSort("id", true, true);
		return search(search);
	}
	/**
	 * get Documents By Pager
	*@author songduanlei@gmail.com
	*@param userid
	*@param isSign
	*@param isView
	*@param pageSize 每页显示数量
	*@param pageNumber 当前第几页
	*@return
	 */
	public List<Document> getDocumentsByPager(Integer userid, Byte isSign, Byte isView,int currentPage,int pageSize){
		Search search = new Search();
		search.addFilterEqual("detailUserByUserId.id", userid);
		search.addFilterEqual("isSign", isSign);
		search.addFilterEqual("isView", isView);
		search.addSort("id", true, true);
		search.setPage(currentPage-1);
		//System.out.println("***********************************************");
		//System.out.println(currentPage);
		search.setMaxResults(pageSize);		
		//System.out.println(pageSize);
		return search(search);
	}
	/**
	 * count documents
	*@author aaron.lu(luzhiyuan)
	*@param userid
	*@param isSign
	*@param isView
	*@return
	 */
	public int countDocuments(Integer userid, Byte isSign, Byte isView){
		Search search = new Search();
		search.addFilterEqual("detailUserByUserId.id", userid);
		search.addFilterEqual("isSign", isSign);
		search.addFilterEqual("isView", isView);
		return count(search);
	}
	
	/**
	 * load hospital's documents 
	*@author liming.yu(yulm88@gmail.com)
	*@param hospitalid 医院编号
	*@param isSign  是否签名
	*@param isView   
	*@return
	 */
	public List<Document> getHospitalDocuments(Integer hospitalid, Byte isSign, Byte isView){
		Search search = new Search();
		search.addFilterEqual("hospital.id", hospitalid);
		search.addFilterEqual("isSign", isSign);
		//search.addFilterEqual("isView", isView);
		return search(search);
	}
	
	/**
	 * count hospital's documents 
	*@author liming.yu(yulm88@gmail.com)
	*@param hospitalid 医院编号
	*@param isSign  是否签名
	*@param isView   
	*@return
	 */
	public int countHospitalDocuments(Integer hospitalid, Byte isSign, Byte isView){
		Search search = new Search();
		search.addFilterEqual("hospital.id", hospitalid);
		search.addFilterEqual("isSign", isSign);
		//search.addFilterEqual("isView", isView);
		
		return count(search);
	}
	
	/**
	 * <p>查询医院所有文档列表
	*@author aaron.lu(luzhiyuan)
	*@param hospital_id
	*@return
	 */
	public List<Document> getAllDocumentInHospital(Integer hospital_id){
		Search search = new Search();
		search.addFilterEqual("hospital.id",hospital_id);
		return super.search(search);
	}
	
	/**
	 * <p>使用存储过程查询统计报表
	*@author aaron.lu(luzhiyuan)
	*@param hospital_id
	*@param order_flag
	*@return
	 */
	public List<UserAndDocumentWrapper> getDocuemntSum(final Integer hospital_id, final String order_flag){
		logger.info("CALL hospiatl_count("+hospital_id+","+order_flag+");");
		return (List<UserAndDocumentWrapper>)jdbcTemplate.execute(new PreparedStatementCreator(){
			public PreparedStatement createPreparedStatement(final Connection connection) throws SQLException {
				final StringBuffer sql = new StringBuffer("CALL hospiatl_count(?,?);");
				final CallableStatement cstmt = connection.prepareCall(sql.toString());
				cstmt.setObject(1, hospital_id);
				cstmt.setObject(2, order_flag);
				return cstmt;
			}
		}, new PreparedStatementCallback(){
			public Object doInPreparedStatement(final PreparedStatement preparedstatement) throws SQLException, DataAccessException {
				boolean flag = preparedstatement.execute();
				ResultSet rs = preparedstatement.getResultSet();
			    ResultSetMetaData metadata = preparedstatement.getMetaData();
				return wrapperPojo(rs, metadata);
			}
		});
	}
	
	/**
	 * <p>将结果集包装成对象
	*@author aaron.lu(luzhiyuan)
	*@param rs
	*@param metadata
	*@return
	*@throws SQLException
	 */
	private List<UserAndDocumentWrapper> wrapperPojo(ResultSet rs, ResultSetMetaData metadata) throws SQLException{
		List<UserAndDocumentWrapper> docs = new ArrayList<UserAndDocumentWrapper>();
		while (rs.next()) {
			UserAndDocumentWrapper userAndDocumentWrapper = new UserAndDocumentWrapper();
			userAndDocumentWrapper.setUsername(rs.getObject(2).toString());
			userAndDocumentWrapper.setAllDocumentCount(new Integer(rs.getObject(3).toString()));
			userAndDocumentWrapper.setSignedDocumentCount(new Integer(rs.getObject(4).toString()));
			userAndDocumentWrapper.setUnSingDocumentCount(new Integer(rs.getObject(5).toString()));
			userAndDocumentWrapper.setReceivedDocumentCount(new Integer(rs.getObject(6).toString()));
			userAndDocumentWrapper.setUnreceiveDocumentCount(new Integer(rs.getObject(7).toString()));
			if (StringHelper.isNotEmpty(userAndDocumentWrapper.getUsername())) {
				docs.add(userAndDocumentWrapper);
			}
		}
		
		return docs;
	}
	
	/***
	 * 更新document
	 * 
	 * **/
	public boolean saveOrUpdate(Document document){
		return super._saveOrUpdateIsNew(document);
	}

	public List<Document> getDocumentByPatient(String caseNumber, int currentPage, int pageSize) {
		Search search = new Search() ;
		search.addFilterEqual("caseNumber", caseNumber);
		search.setPage(currentPage - 1) ;
		search.setMaxResults(pageSize);
		return search(search);
	}

	public int getDocumentNumByPatient(String caseNumber) {
		Search search = new Search() ;
		search.addFilterEqual("caseNumber", caseNumber);
		return count(search);
	}

	public List<Document> getSignDocumentByPatient(String caseNumber,byte isSign, int currentPage, int pageSize) {
		Search search = new Search();
		search.addFilterEqual("caseNumber", caseNumber);
		search.addFilterEqual("isSign",isSign);
		search.setPage(currentPage-1);
		search.setMaxResults(pageSize);
		return search(search);
	}

	public int getSignDocumentNumByPatient(String caseNumber, byte isSign) {
		Search search = new Search() ;
		search.addFilterEqual("caseNumber", caseNumber);
		search.addFilterEqual("isSign", isSign);
		return count(search);
	}

	@SuppressWarnings("unchecked")
	public List<PatientDocument> getPatient(final int currentPage, final int pageSize) {
		String sql = "SELECT DISTINCT(case_number), patient FROM document" ;
		final List<PatientDocument> list = new ArrayList<PatientDocument>();
		jdbcTemplate.query(sql, new RowCallbackHandler() {
			public void processRow(ResultSet rs) throws SQLException {
				Document doc = new Document();
				doc.setCaseNumber(rs.getString("case_number"));
				doc.setPatient(rs.getString("patient"));
				PatientDocument pdoc = new PatientDocument();
				pdoc.setDocument(doc);
				int docNumber = getDocumentNumByPatient(doc.getCaseNumber());
				byte isSign = 1 ;
				int signDocNumber = getSignDocumentNumByPatient(doc.getCaseNumber(), isSign);
				isSign = 0 ;
				int unSignDocNumber = getSignDocumentNumByPatient(doc.getCaseNumber(),isSign);
				pdoc.setDocNumber(docNumber);
				pdoc.setSignDocNumber(signDocNumber);
				pdoc.setUnSignDocNumber(unSignDocNumber);
				list.add(pdoc);
			}
		});
		return list ;
	}

	public int getPatientNum() {
		String sql = "SELECT COUNT(DISTINCT(case_number)) FROM document ";
		return jdbcTemplate.queryForInt(sql);
	}

	public List<PatientDocument> getDocumentByCondition(PatientDocument patientDocument, int currentPage, int pageSize) {
		final List<PatientDocument> patientDocuments = new ArrayList<PatientDocument>();
		StringBuffer sql = new StringBuffer() ;
		sql.append("SELECT ");
		sql.append("a.id, ");
		sql.append("a.patient, ");
		sql.append("a.create_time, ");
		sql.append("a.operate_time, ");
		sql.append("a.sign_time, ");
		sql.append("a.case_number ,");
		sql.append("b.family_name ");
		sql.append("FROM ");
		sql.append("document a ,");
		sql.append("detail_user b ");
		sql.append("WHERE ");
		sql.append(" 1=1 ");
		if ( null != patientDocument ) {
			String patient = patientDocument.getPatientName();
			if ( null != patient && ! "".equals(patient.trim())) {
				patient = "'%" + patient + "%'";
				sql.append("AND a.patient LIKE " + patient +" ");
			}
			String caseNumber = patientDocument.getCaseNumber();
			if ( null != caseNumber && ! "".equals(caseNumber.trim())) {
				caseNumber = "'%" + caseNumber + "%'";
				sql.append("AND a.case_number LIKE " + caseNumber + " ");
			}
			String createTime = patientDocument.getCreateTime();
			if ( null != createTime && ! "".equals(createTime.trim())){
				sql.append("AND DATE_FORMAT(a.create_time,'%Y%m%d') = " + createTime + " ");
			}
			String operateTime = patientDocument.getReceiveTime();
			if ( null != operateTime && ! "".equals(operateTime.trim())) {
				sql.append("AND DATE_FORMAT(a.operate_time,'%Y%m%d') = " + operateTime + " ");
			}
			String signTime = patientDocument.getSignTime() ;
			if ( null != signTime && ! "".equals(signTime.trim())) {
				sql.append("AND DATE_FORMAT(a.sign_time,'%Y%m%d') = " + signTime + " ");
			}
			byte isSign = patientDocument.getIsSign();
			sql.append("AND a.is_sign = " + isSign + " ");
			byte isView = patientDocument.getIsSign();
			sql.append("AND a.is_view = " + isView + " ");
			String familyName = patientDocument.getFamilyName();
			if ( null != familyName && ! "".equals(familyName.trim())) {
				familyName = "'%" + familyName + "%'";
				sql.append("AND b.family_name LIKE " + familyName + " " );
			}
			sql.append("LIMIT ");
			sql.append( (currentPage-1) * pageSize + ",");
			sql.append( pageSize);
		}
		jdbcTemplate.query(sql.toString(), new RowCallbackHandler() {
			public void processRow(ResultSet rs) throws SQLException {
				Document d = new Document();
				d.setId(rs.getInt("id"));
				d.setPatient(rs.getString("patient"));
				d.setCaseNumber(rs.getString("case_number"));
				d.setCreateTime(rs.getDate("create_time"));
				d.setOperateTime(rs.getDate("operate_time"));
				d.setSignTime(rs.getDate("sign_time"));
				PatientDocument pd = new PatientDocument();
				pd.setDocument(d);
				pd.setFamilyName(rs.getString("family_name"));
				patientDocuments.add(pd);
			}
		});
		return patientDocuments ;	
	}

	public int getDocumentNumByCondition(PatientDocument patientDocument) {
		StringBuffer sql = new StringBuffer() ;
		sql.append("SELECT ");
		sql.append("COUNT(a.id) ");
		sql.append("FROM ");
		sql.append("document a, ");
		sql.append("detail_user b ");
		sql.append("WHERE ");
		sql.append(" 1=1 ");
		if ( null != patientDocument ) {
			String patient = patientDocument.getPatientName();
			if ( null != patient && ! "".equals(patient.trim())) {
				patient = "'%" + patient + "%'";
				sql.append("AND a.patient LIKE " + patient +" ");
			}
			String caseNumber = patientDocument.getCaseNumber();
			if ( null != caseNumber && ! "".equals(caseNumber.trim())) {
				caseNumber = "'%" + caseNumber + "%'";
				sql.append("AND a.case_number LIKE " + caseNumber + " ");
			}
			String createTime = patientDocument.getCreateTime();
			if ( null != createTime && ! "".equals(createTime.trim())){
				sql.append("AND DATE_FORMAT(a.create_time,'%Y%m%d') = " + createTime + " ");
			}
			String operateTime = patientDocument.getReceiveTime();
			if ( null != operateTime && ! "".equals(operateTime.trim())) {
				sql.append("AND DATE_FORMAT(a.operate_time,'%Y%m%d') = " + operateTime + " ");
			}
			String signTime = patientDocument.getSignTime() ;
			if ( null != signTime && ! "".equals(signTime.trim())) {
				sql.append("AND DATE_FORMAT(a.sign_time,'%Y%m%d') = " + signTime + " ");
			}
			byte isSign = patientDocument.getIsSign();
			sql.append("AND a.is_sign = " + isSign + " ");
			byte isView = patientDocument.getIsSign();
			sql.append("AND a.is_view = " + isView + " ");
			String familyName = patientDocument.getFamilyName();
			if ( null != familyName && ! "".equals(familyName.trim())) {
				familyName = "'%" + familyName + "%'";
				sql.append("AND b.family_name LIKE " + familyName + " " );
			}
		}
		return jdbcTemplate.queryForInt(sql.toString());
	}
}
